﻿using DuckDB.NET.Data;
using System.Collections.Concurrent;
using OfficeOpenXml;  // 使用EPPlus库
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace LogServerApi.Services
{
	public class LogService : BackgroundService, ILogService
	{
		private readonly IConfiguration _configuration;
		private readonly ILogger<LogService> _logger;

		private readonly ConcurrentQueue<LogPushRequest> _logQueue;
		private readonly string _dbPath;

		public LogService(IConfiguration configuration, ILogger<LogService> logger)
		{
			_configuration = configuration;
			_logger = logger;
			_logQueue = new ConcurrentQueue<LogPushRequest>();

			var logPath = _configuration["LogOptions:LogFile"] ?? "log.duckdb";
			logPath = logPath.Replace('/', Path.DirectorySeparatorChar).Replace('\\', Path.DirectorySeparatorChar);
			_dbPath = Utils.GetRealPath(logPath, Utils.GetEntryFolder());
			var folder = Path.GetDirectoryName(_dbPath);
            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder!);
            }
            _logger.LogInformation("Using database path: {}", _dbPath);
			ExcelPackage.LicenseContext = LicenseContext.NonCommercial;  // 非商业用途
		}

		protected override async Task ExecuteAsync(CancellationToken stoppingToken)
		{
			using var ddbc = new DuckDBConnection($"Data Source={_dbPath}");
			ddbc.Open();

			using var command = ddbc.CreateCommand();
			command.CommandText = @"CREATE TABLE IF NOT EXISTS logs (Timestamp TIMESTAMP, PlayerId TEXT, Message JSON);";
			var executeNonQuery = command.ExecuteNonQuery();
			_logger.LogInformation("Database initialized check result: {}", executeNonQuery);

			const int maxBatchSize = 100;
			while (!stoppingToken.IsCancellationRequested)
			{
				var counter = 0;

				while (counter < maxBatchSize && _logQueue.TryDequeue(out var log))
				{
					counter++;
					_logger.LogInformation("Processing log");
					var dtUtc = new DateTime(log.UtcTick, DateTimeKind.Utc);
					var timestamp = dtUtc.ToString("yyyy-MM-ddTHH:mm:ss.fffffffZ");
					command.CommandText = $"INSERT INTO logs VALUES ('{timestamp}', '{log.PlayerId}', '{log.Message}')";
					executeNonQuery = command.ExecuteNonQuery();
					_logger.LogDebug("Log inserted check result: {}", executeNonQuery);
				}

				await Task.Delay(1000, stoppingToken);
			}
		}

		public void PushLog(LogPushRequest log)
		{
			_logger.LogInformation("Pushing log");
			_logQueue.Enqueue(log);
		}

		Task<byte[]> ILogService.DownloadExcel()
		{
			using var ddbc = new DuckDBConnection($"Data Source={_dbPath}");
			ddbc.Open();

			// 1. 获取所有的 PlayerId
			var playerIds = new List<string>();
			using (var command = ddbc.CreateCommand())
			{
				command.CommandText = "SELECT DISTINCT PlayerId FROM logs";  // 使用 PlayerId 列进行查询

				using (var reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						playerIds.Add(reader.GetString(0));
					}
				}
			}

			// 2. 创建一个 Excel 文件
			using (var package = new ExcelPackage())
			{
				foreach (var playerId in playerIds)
				{
					// 获取每个 PlayerId 的所有属性
					var attributes = new HashSet<string>();
					using (var command = ddbc.CreateCommand())
					{
						// 查询条件改为直接使用 PlayerId 列
						command.CommandText = @$"SELECT json_keys(a) FROM (SELECT json_group_structure(Message) AS a FROM logs WHERE PlayerId = '{playerId}')";

						using (var reader = command.ExecuteReader())
						{
							while (reader.Read())
							{
								var list = reader.GetValue(0) as List<string>;
								if (list != null)
								{
									foreach (var item in list)
									{
										attributes.Add(item);
									}
								}
							}
						}
					}

					if (attributes.Count == 0)
						continue;

					// 构造查询字符串
					var columnDefinitions = new List<string> { "Timestamp", "PlayerId" };  // 添加 PlayerId 列
					foreach (var attribute in attributes)
					{
						columnDefinitions.Add($"json_extract(Message, '$.{attribute}') AS {attribute}");
					}

					string selectClause = string.Join(", ", columnDefinitions);
					string query = $@"
                SELECT {selectClause}
                FROM logs
                WHERE PlayerId = '{playerId}'";  // 查询条件改为直接使用 PlayerId 列

					// 3. 执行查询并生成数据
					using (var command = ddbc.CreateCommand())
					{
						command.CommandText = query;
						using (var reader = command.ExecuteReader())
						{
							// 4. 创建一个新的 Excel Sheet
							var worksheet = package.Workbook.Worksheets.Add(playerId);
							var row = 1;

							// 写入表头（列名）
							for (int col = 0; col < reader.FieldCount; col++)
							{
								worksheet.Cells[row, col + 1].Value = reader.GetName(col);
							}

							// 写入数据
							while (reader.Read())
							{
								row++;
								for (int col = 0; col < reader.FieldCount; col++)
								{
									if (col == 0)
									{
										worksheet.Cells[row, col + 1].Value = reader.IsDBNull(col) ? "" : reader.GetDateTime(col).ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss");
									}
									else worksheet.Cells[row, col + 1].Value = reader.IsDBNull(col) ? "" : reader.GetValue(col);
								}
							}
						}
					}
				}

				return package.GetAsByteArrayAsync();
			}
		}

	}
}
